<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/6/7 0007
 * Time: 上午 11:07
 */


namespace app\modules\myyii\controllers;
use app\models\Contract;
use app\models\ContractSearch;
use app\models\AttrSpec;
use app\models\TheSubject;
use app\models\OrderResumes;
use app\models\ProdInst;
use yii;
use yii\helpers\DateTime;
use kartik\date\DatePicker;
use yii\helpers\Json;
use yii\helpers\ArrayHelper;
use yii\data\Pagination;//分页
use app\common\Controller;
use app\models\Cust;
use app\models\CustomTableView;//引用
use app\models\ProdSpec;
use app\models\ProdInstSearch;
use app\models\OrderSearch;
use app\models\Wj;
use app\models\WjSearch;
use app\models\Order;
use app\models\CustomTableViewSearch;
use app\models\Staff;
use app\models\Region;
use app\models\CustSource;
use app\models\CustExternalAttr;
use app\models\Domain;
use app\models\OrderExternalAttr;
use app\common\Functions;
use moonland\phpexcel\Excel;
use PHPExcel;
//use common\components\Phpexcel;
use scotthuangzl\export2excel\Export2ExcelBehavior;




class MyyiiController  extends Controller {

    public $module;
    public $protected_uids = [];
    public $superadmin_uid = 0;
    public $loginRedirect = '/admin/dashboard';
    public $defaultAction = 'index';
    public function init()
    {
        parent::init();
    }

    public function actionIndex($object_id = null ,$time = null ,$a_time=null )//进入页面
    {
//        $session=new CHttpSession;
//        $session->open();
//        $value1=$session['name1'];
//        unset($session[$sessionKey]);//超过限制时间，释放session";

        $session = Yii::$app->session;
        $session['time'] = $time;
        $session['a_time'] = $a_time;

        $lendedAreas=$this->select_table($time  ,$a_time );
        return $this->render('index', [
            'regions' => $lendedAreas ]);

                                      //传递多个变量时用逗号隔开


    }


    public  function actionExport( )//生成Excel
    {
        $objectPHPExcel = new PHPExcel();
        $objectPHPExcel->setActiveSheetIndex(0);

        $page_size = 52;
        $n = 0;
        $current_page = 0;

        $session = Yii::app()->session;
        $time=$session['time'];
        $a_time=$session['a_time'] ;

        $data=$this->select_table($time  ,$a_time );
        foreach ($data as $k=>$product) {
            if ($n % $page_size === 0) {
                $current_page = $current_page + 1;

                //报表头的输出
                $objectPHPExcel->getActiveSheet()->mergeCells('B1:G1');
                $objectPHPExcel->getActiveSheet()->setCellValue('B1', '客户信息表');

                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '客户信息表');
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '客户信息表');
                $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getFont()->setSize(24);
                $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B1')
                    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', '日期：' . date("Y年m月j日"));
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', '第' . $current_page . '/' . $page_count . '页');
                $objectPHPExcel->setActiveSheetIndex(0)->getStyle('G2')
                    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

                //表格头的输出
                $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B3', '编号');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6.5);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C3', '公司');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('D3', '录入时间');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('E3', '咨询方式');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('F3', '姓名');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G3', '手机');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('H3', '需求面积');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('I3', '意向区域');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('J3', '租金预算');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('K3', '行业类型');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('L3', '信息来源');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('M3', '装修');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('N3', '状态');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('O3', '招商员');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('P3', '录入员');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('Q3', '咨询时间');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('R3', '客源编号');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('S3', '备注');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);



                //设置居中
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                //设置边框
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')
                    ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

                //设置颜色
                $objectPHPExcel->getActiveSheet()->getStyle('B3:S3')->getFill()
                    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF66CCCC');

            }
            //明细的输出
            $objectPHPExcel->getActiveSheet()->setCellValue('B' . ($n + 4), $product->id);
            $objectPHPExcel->getActiveSheet()->setCellValue('C' . ($n + 4), $product->product_name);
            $objectPHPExcel->getActiveSheet()->setCellValue('D' . ($n + 4), $product->product_agent->name);
            $objectPHPExcel->getActiveSheet()->setCellValue('E' . ($n + 4), $product->unit);
            $objectPHPExcel->getActiveSheet()->setCellValue('F' . ($n + 4), $product->unit_price);
            $objectPHPExcel->getActiveSheet()->setCellValue('G' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('H' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('I' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('J' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('K' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('L' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('M' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('N' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('O' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('P' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('Q' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('R' . ($n + 4), $product->library_count);
            $objectPHPExcel->getActiveSheet()->setCellValue('S' . ($n + 4), $product->library_count);

            //设置边框
            $currentRowNum = $n + 4;
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($n + 4) . ':S' . $currentRowNum)
                ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($n + 4) . ':S' . $currentRowNum)
                ->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($n + 4) . ':S' . $currentRowNum)
                ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($n + 4) . ':S' . $currentRowNum)
                ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $objectPHPExcel->getActiveSheet()->getStyle('B' . ($n + 4) . ':S' . $currentRowNum)
                ->getBorders()->getVertical()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            $n = $n + 1;
        }

        //设置分页显示
        //$objectPHPExcel->getActiveSheet()->setBreak( 'I55' , PHPExcel_Worksheet::BREAK_ROW );
        //$objectPHPExcel->getActiveSheet()->setBreak( 'I10' , PHPExcel_Worksheet::BREAK_COLUMN );
        $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
        $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);


        ob_end_clean();
        ob_start();

        header('Content-Type : application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename="' . '产品信息表-' . date("Y年m月j日") . '.xls"');
        $objWriter = PHPExcel_IOFactory::createWriter($objectPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }


    //封装函数
    public function select_table($time = null ,$a_time=null){
        $sql  = "SELECT o.create_date As time,";
        $sql .= "dm44.domain_name As conm,";
        $sql .="c.cust_name As name,";
        $sql .="o3.content As cell  ,";
        $sql .="CONCAT(o11.content, \"-\",o11.content2) As actual,";
        $sql .=" r.region_name As yx,";
        $sql .="CONCAT(o23.content, \"-\",o23.content2) As rent,";
        $sql .="dm24.domain_name As industry,";
        $sql .="cs2.cust_source_name As inf,";
        $sql .="cs.cust_source_name As source,";
        $sql .="dm25.domain_name As finish,";
        $sql .="dmst.domain_name As zt,";
        $sql .="s.staff_name  As attract, ";
        $sql .="s.staff_name  As keyboarder, ";
        $sql .="o.input_time  As input_time, ";
        $sql .="o.id  As num, ";
        $sql .=" o.remark As remark  FROM";
        $sql .= Order::tableName();
        $sql .= " as o  LEFT JOIN";
        $sql .= Staff::tableName() ;
        $sql .= " as s on o.create_staff_id = s.user_id";
        $sql .= " LEFT JOIN ";
        $sql .= Region::tableName() ;
        $sql .= " as r on o.region_id = r.id";
        $sql .= " LEFT JOIN ";
        $sql .= CustExternalAttr::tableName() ;
        $sql .= " as o3 on o.object_id = o3.cust_id  and o3.attr_spec_id = 3";
        $sql .= " LEFT JOIN ";
        $sql .= Cust::tableName() ;
        $sql .= " as c on o.object_id = c.id";
        $sql .= " LEFT JOIN ";
        $sql .= CustSource::tableName() ;
        $sql .= " as cs on cs.id  = c.cust_source_id";
        $sql .= " LEFT JOIN ";
        $sql .= CustSource::tableName() ;
        $sql .= " as cs2 on cs2.id  = cs.parent_source_id";
        $sql .= " LEFT JOIN ";
        $sql .= OrderExternalAttr::tableName() ;
        $sql .= " as o11 on o.id = o11.order_id  and o11.attr_spec_id = 11";
        $sql .= " LEFT JOIN ";
        $sql .= OrderExternalAttr::tableName() ;
        $sql .= " as o23 on  o.id = o23.order_id   and o23.attr_spec_id = 23";
        $sql .= " LEFT JOIN ";
        $sql .= CustExternalAttr::tableName() ;
        $sql .= " as o24 on o.object_id = o24.cust_id  and o24.attr_spec_id = 24";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as dm24 on dm24.domain_code = o24.content";
        $sql .= " LEFT JOIN ";
        $sql .= OrderExternalAttr::tableName() ;
        $sql .= " as o44 on  o.id = o44.order_id   and o44.attr_spec_id = 44";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as dm44 on dm44.domain_code = o44.content";
        $sql .= " LEFT JOIN ";
        $sql .= OrderExternalAttr::tableName() ;
        $sql .= " as o25 on  o.id = o25.order_id   and o25.attr_spec_id = 25";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as dm25 on dm25.domain_code = o25.content";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as dmst on dmst.domain_code = o.state";
        $sql .=  " WHERE  o.object_type_id = 1 and o.create_date between  date_format(:b_create_date, '%Y%m%d%H%i%s') and  date_format(:e_create_date, '%Y%m%d%H%i%s') order by o.id desc ";

        //绑定变量并执行查询动作, 注意取出的$lendedAreas是一个数组,需要用$lendedAreas['别名']才能获得具体的值
        //$today = date("Y-m-d",time());//当前日期

        if(empty($time) && empty($a_time)){//都为空

            $time = date("Y-m",strtotime("last month"));//上一个月
            $datetimes = date("Y-m",time());//当前日期

            $lendedAreas = Yii::$app->db->createCommand($sql)
//            ->bindValue(':b_create_date',$begin_datetime)
//            ->bindValue(':e_create_date', $end_datetime )
                ->bindValue(':b_create_date',$time.'-01 00:00:01')
                ->bindValue(':e_create_date', $datetimes.'-01 00:00:01')
                ->query();
        }
        elseif(!empty($time) && empty($a_time)){//月份按钮
            //$datetimes = new \DateTime($time);//自定义时间
            $datetime = new \DateTime($time);
            $interval = \DateInterval::createFromDateString('+1 month');
            $period = new \DatePeriod($datetime, $interval, 1);
            foreach ($period as $date) {
                $datetimes= $date->format('Y-m');

            }
            $lendedAreas = Yii::$app->db->createCommand($sql)
//            ->bindValue(':b_create_date',$begin_datetime)
//            ->bindValue(':e_create_date', $end_datetime )
                ->bindValue(':b_create_date',$time.'-01 00:00:01')
                ->bindValue(':e_create_date', $datetimes.'-01 00:00:01')
                ->query();

        }else{//自定义时间
            $time = $a_time;//某天
            $datetime = new \DateTime($time);
            $interval = \DateInterval::createFromDateString('+1 day');
            $period = new \DatePeriod($datetime, $interval, 1);
            foreach ($period as $date) {
                $datetimes= $date->format('Y-m-d');
            }
            //+1天
            $lendedAreas = Yii::$app->db->createCommand($sql)

                ->bindValue(':b_create_date',$a_time .' 00:00:01')
                ->bindValue(':e_create_date', $datetimes .' 00:00:01')
                ->query();
        }
//        $begin_datetime = "2017-06-15 00:00:01";
//        $end_datetime = "2017-06-16 00:00:01";

//        foreach ($lendedAreas as $k => $v ){
//            Yii::trace( "conm :", $v['conm']);
//            Yii::trace( "k :", $k);
//        }
        //->queryOne(); // 绑定需要一一对应（一条计入）


//        Yii::trace("++++++++++++++++++++++++++++++++++++++++>");
//        Yii::trace($lendedAreas);
//        Yii::trace("============================================>");
//        Yii::trace($datetime);
//        Yii::trace("------------------------------------------------>");
//        Yii::trace($datetimes);
//        Yii::trace("+-+-+-+-+-+-+-+-+++-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+>");
//        Yii::trace($interval);
//         Yii::trace("++++++++++++++++++++++++++++++++++++++++>");
//        Yii::trace($period);
//        Yii::trace($a_time);
        return $lendedAreas;
    }

    //个人工作统计
    public function actionTable(){

        $datetimes = date("Y-m-d H:i:s",time());//当前日期
        $datetimed = date("Y-m",time());
        $datetimed = $datetimed .'-01 00:00:01';//月初日期

        // 获取汇总信息（）
        $buildingInfo = [];

        //1.个人录入房源统计；（全部）
        $id=Yii::$app->user->id;
        $query = ProdInst::find()->alias('a');
        $query->where(['create_staff_id' => $id , 'prod_spec_id'=>10002 ]);
        $buildingInfo['prod_inst_staff']=$query->count();

        //2.个人录入客源统计；（全部）
        $id=Yii::$app->user->id;
        $query = Cust::find()->alias('a');
        $query->where(['create_staff_id' => $id ]);
        $buildingInfo['cust_staff'] = $query->count();

        //3.个人本月处理中的订单统计（月）


        $id=Yii::$app->user->id;
        $query = Order::find()->alias('a');
        $query->andFilterWhere(['staff_id' => $id ,'state'=>30001 ]);
        $query->andFilterWhere(['AND',['>','create_date',$datetimed] ,['<','create_date',$datetimes]]);
        $buildingInfo['order_processed'] = $query->count();

        //4.个人本月处理完成的订单统计（月）

        $id=Yii::$app->user->id;
        $query = Order::find()->alias('a');
        $query->andFilterWhere(['staff_id' => $id ,'state'=>30002 ]);
        $query->andFilterWhere(['AND',['>','create_date',$datetimed] ,['<','create_date',$datetimes]]);
        $buildingInfo['order_complete']=$query->count();

        //5.个人剩余的订单份额统计(全部)
        $id=Yii::$app->user->id;
        $query = Order::find()->alias('a');
        $query->where(['staff_id' => $id ,'state'=>30001 ]);
        $buildingInfo['order_residue']=$query->count();

        return $this->render('table',
            [
                'buildingInfo'=>$buildingInfo ]);
        //传递多个变量时用逗号隔开
    }


    public function actionTables() {

        $buildingInfo=666;
        return $this->render('table_style',
            [
                'buildingInfo'=>$buildingInfo ]);

    }

    //跟进报表统计
    public function actionTabledata() {

        $sql  = "SELECT a.resumes_type_id,";
        $sql .= "a.staff_id, ";
        $sql .= "a.create_staff_id,";
        $sql .= "a.create_date ,";
        $sql .= "DATE_FORMAT( a.create_date ,'%Y-%m-%d') as createDay,";
        $sql .= "a.remark ,";
        $sql .= "c.staff_name ,";
        $sql .= "d.staff_name as create_staff_name, ";
        $sql .= "a.last_state ,";
        $sql .= "a.next_state  FROM";
        $sql .= OrderResumes::tableName() ;
        $sql .= " as a  LEFT JOIN";
        $sql .= Order::tableName();
        $sql .= " as b on a.order_id = b.id";
        $sql .= " LEFT JOIN ";
        $sql .= Staff::tableName() ;
        $sql .= " as c on  c.user_id = a.staff_id ";
        $sql .= " LEFT JOIN ";
        $sql .= Staff::tableName() ;
        $sql .= " as d on  d.user_id = a.create_staff_id ";

        $sql .=  " where   b.object_id =:b_create_date  and b.id  =:e_create_date   order by a.create_date desc ";

        $lendedAreas = Yii::$app->db->createCommand($sql)
        ->bindValue(':b_create_date','10001266')
        ->bindValue(':e_create_date','115' )
        ->query();



//        $pagination = new Pagination([//new Pagination定义分页
//            'defaultPageSize' => 5,//每页5条
//            'totalCount' => $lendedAreas->count(),//总数
//        ]);
//
//        $regions = $lendedAreas->orderBy('create_date')->offset($pagination->offset)->limit($pagination->limit)->all();


        $buildingInfo=$lendedAreas;
        return $this->render('index_context',
            [
                'buildingInfo'=>$buildingInfo  ]);

    }


    //2部月度排行版
    public function actionRanking($txt)
    {
        $user_id=Yii::$app->user->id;

        $datetimed= date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
        $datetimes= date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));

        $ranking=$this->getDate($user_id,$datetimed,$datetimes);
        return $this->render('table_style',
            [
                'ranking'=>$ranking]);

    }
    //一部
    public function actionRankingSelf($txt)
    {
        $user_id=Yii::$app->user->id;

        $datetimed= date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),1,date("Y")));
        $datetimes= date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("t"),date("Y")));

        $ranking=$this->getDate($user_id,$datetimed,$datetimes);
        return $this->render('table_style',
            [
                'ranking'=>$ranking]);

    }
    public function actionRankings($txt,$time=null)
    {
        $user_id=Yii::$app->user->id;
        if($txt==1){
            $c= date('Y',time());
        }

        if($txt==2){//季度
            $getMonthDays = date("t",mktime(0, 0 , 0,date('n')+(date('n')-1)%3,1,date("Y")));//本季度未最后一月天数
            $datetimed= date('Y-m-d H:i:s', mktime(0, 0, 0,date('n')-(date('n')-1)%3,1,date('Y')));
            $datetimes= date('Y-m-d H:i:s', mktime(23,59,59,date('n')+(date('n')-1)%3,$getMonthDays,date('Y')));
            $ranking=$this->getDate($user_id,$datetimed,$datetimes);
            return $this->renderAjax('table_styles', [ 'ranking'=>$ranking ]);

        }elseif($txt==3){//年度
            $c= date('Y',time());
            $datetimed=$c.'-01-01 00:00:00';
            $datetimes=$c.'-12-31 23:59:59';
            $ranking=$this->getDate($user_id,$datetimed,$datetimes);
            return $this->renderAjax('table_styles', [ 'ranking'=>$ranking ]);
        }


    }

    public function getDate($user_id,$time1,$time2){
        $ranking=[];
        $staff_group=Functions::getMyid();

        $ranking['name']=$this->getUser($user_id);//没有数据时用 这个默认的

        $ranking['order']=$this->getOrder(null,$time1,$time2,$staff_group);//订单榜
        $ranking['cust']=$this->getProd(null,$time1,$time2,$staff_group);//客户榜
        $ranking['prod_inst']=$this->getTotalTao(null,$time1,$time2,$staff_group);//房源排行

        $ranking['user_order']=$this->getOrder($user_id,$time1,$time2,$staff_group);//本人本月订单
        $ranking['user_cust']=$this->getProd($user_id,$time1,$time2,$staff_group);
        $ranking['user_prod_inst']=$this->getTotalTao($user_id,$time1,$time2,$staff_group);//本人本月开发房


        $ranking['order_endue']=$this->getForeach_arry($ranking['order']);//填充缺失的空数组
        $ranking['cust_endue']=$this->getForeach_arry($ranking['cust']);
        $ranking['prod_inst_endue']=$this->getForeach_arry($ranking['prod_inst']);

        return $ranking;
    }


    public function getForeach_arry($ranking){

        $rankings=['name'=>'空','dize'=>'空','sum'=>0 ,'img'=>'/uploads/tuxiang.jpg'];

        if (!$ranking)
        {
            $ranking=[];
        }
        $rankingd=[];
        foreach($ranking as $order)
        {
            $order2 = [];

            $order2['name'] = $order['name'];
            $order2['dize'] = $order['dize'];

            $order2['sum'] = $order['sum'];
            $order2['img'] = $order['img'];

            $rankingd[] = $order2;

        }
        $j = count($rankingd);
        if(count($rankingd)<10 ){

            for( $i=0;$i<10-$j;$i++){

                $rankingd[]=$rankings;

            }
        }
        return $rankingd;

    }

    public function getUser($id){
        $sql  = "SELECT z.staff_name As name,";
        $sql .= "o.domain_name As dize FROM";
        $sql .= Staff::tableName();
        $sql .= " as z LEFT JOIN";
        $sql .= Domain::tableName() ;
        $sql .= " as o on  o.domain_code = z.sub_staff_group ";
        $sql .=  " WHERE  z.user_id=:a";
        $lendedAreas = Yii::$app->db->createCommand($sql)
            ->bindValue(':a',$id)
            ->queryOne();


        return $lendedAreas;
    }
    //获取本月开发房源多少套
    public function getTotalTao($id,$time,$times,$staff_group)
    {
        $sql  = "SELECT z.staff_name As name,";
        $sql .= "o.domain_name As dize, ";
        $sql .= "z.img As img, ";
        $sql .= "COUNT(a.id) As sum  FROM";
        $sql .= ProdInst::tableName() ;
        $sql .= " as a  LEFT JOIN";
        $sql .= Staff::tableName();
        $sql .= " as z on a.create_staff_id = z.user_id";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as o on  o.domain_code = z.sub_staff_group ";
        $sql .=  " WHERE ";
        if($id){
            $sql .=  "(a.create_staff_id=:a) AND";
            $sql .=" (a.prod_spec_id=10002) and z.staff_group=:d and a.create_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s') group by `a`.`create_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10 ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':a',$id)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->queryOne();
        }else{
            $sql .=" (a.prod_spec_id=10002) and z.staff_group=:d and a.create_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s') group by `a`.`create_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10 ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->query();
        }


        return $lendedAreas;

    }
    //本月成交订单
    public function getOrder($id,$time,$times,$staff_group){
        $sql  = "SELECT z.staff_name As name,";
        $sql .= "o.domain_name As dize, ";
        $sql .= "z.img As img, ";
        $sql .= "COUNT(a.id) As sum  FROM";
        $sql .= Order::tableName() ;
        $sql .= " as a  LEFT JOIN";
        $sql .= Staff::tableName();
        $sql .= " as z on a.modify_staff_id = z.user_id";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as o on  o.domain_code = z.sub_staff_group ";
        $sql .=  " WHERE ";
        if($id){
            $sql .=  " (a.create_staff_id=:a) AND(a.state=30002) and z.staff_group=:d and a.create_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s')group by `a`.`modify_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10  ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':a',$id)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->queryOne();
        }else{
            $sql .=  "(a.state=30002) and z.staff_group=:d and a.modify_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s')group by `a`.`modify_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10  ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->query();
        }


        return $lendedAreas;
    }
    public function getProd($id,$time,$times,$staff_group){
        $sql  = "SELECT z.staff_name As name,";
        $sql .= "o.domain_name As dize, ";
        $sql .= "z.img As img, ";
        $sql .= "COUNT(a.id) As sum  FROM";
        $sql .= Order::tableName() ;
        $sql .= " as a  LEFT JOIN";
        $sql .= Cust::tableName() ;
        $sql .= " as b  on a.`object_id` = b.id ";
        $sql .= " LEFT JOIN ";
        $sql .= Staff::tableName();
        $sql .= " as z on a.create_staff_id = z.user_id";
        $sql .= " LEFT JOIN ";
        $sql .= Domain::tableName() ;
        $sql .= " as o on  o.domain_code = z.sub_staff_group ";
        $sql .=  " WHERE ";
        if($id){
            $sql .=  "  z.staff_group=:d and(b.create_staff_id=:a) AND(b.cust_type_id !=106) AND(b.cust_type_id !=105)  and b.create_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s')group by `a`.`create_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10  ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':a',$id)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->queryOne();
        }else{
            $sql .= "z.staff_group=:d and (b.cust_type_id !=106) AND(b.cust_type_id !=105) AND (z.`user_id`!=4) and b.create_date between  date_format(:b, '%Y%m%d%H%i%s') and  date_format(:c, '%Y%m%d%H%i%s') group by `a`.`create_staff_id` ORDER BY COUNT(a.id) DESC LIMIT 10 ";
            $lendedAreas = Yii::$app->db->createCommand($sql)
                ->bindValue(':b',$time)
                ->bindValue(':c',$times )
                ->bindValue(':d',$staff_group )
                ->query();
        }

        return $lendedAreas;
    }



    //转入公盘报表页面（显示不可以转入公盘的员工操作了转入公盘事件）

    public function actionCommonality(){

        $sql = "select A.id AS id,";
        $sql .= "A.remark AS remark,";
        $sql .= "A.time AS time,";
        $sql .= "A.name2 AS name,";
        $sql .= "B.name2 AS name2,";
        $sql .= "TIMESTAMPDIFF(DAY,A.time,B.time) AS time2 ";
        $sql .= " FROM ( SELECT b.order_id as id,";
        $sql .= " b.remark as remark,";
        $sql .= " b.create_date as time,";

        $sql .= " s2.staff_name as name2 FROM ";
        $sql .= OrderResumes::tableName();
        $sql .= " as b LEFT JOIN ";
        $sql .= Staff::tableName();
        $sql .= " as `s` ON `b`.`create_staff_id` = `s`.`user_id`  LEFT JOIN ";
        $sql .= Staff::tableName();
        $sql .= " as `s2` ON `b`.`staff_id` = `s2`.`user_id`";
        $sql .= " WHERE b.`order_id` IN (SELECT a.`order_id` FROM ";
        $sql .= OrderResumes::tableName();
        $sql .= " as a WHERE a.`resumes_type_id`=20003 AND a.`staff_id`=-1 )";
        $sql .= "  AND b.`staff_id`!=-1  AND s2.`shift`=0 and b.`resumes_type_id`=20003  ORDER BY b.`order_id` ASC ) ";
        $sql .= " as A LEFT JOIN ( SELECT b.order_id as id,";
        $sql .= " b.remark as remark,";
        $sql .= " b.create_date as time,";
        $sql .= " s.staff_name as name,";
        $sql .= " s2.staff_name as name2 FROM ";
        $sql .= OrderResumes::tableName();
        $sql .= " as b LEFT JOIN ";
        $sql .= Staff::tableName();
        $sql .= " as `s` ON `b`.`create_staff_id` = `s`.`user_id`  LEFT JOIN ";
        $sql .= Staff::tableName();
        $sql .= " as `s2` ON `b`.`staff_id` = `s2`.`user_id`";
        $sql .= " WHERE b.`order_id` IN (SELECT a.`order_id` FROM ";
        $sql .= OrderResumes::tableName();
        $sql .= " as a WHERE a.`resumes_type_id`=20003 AND a.`staff_id`=-1 )";
        $sql .= "  AND b.`staff_id`=-1  AND s.`shift`=0 and b.`resumes_type_id`=20003  ORDER BY b.`order_id` ASC ) ";
        $sql .= " as B on A.id=B.id";


        $lendedAreas = Yii::$app->db->createCommand($sql)
            ->query();


        return $this->render('commonalitytable',
            [
                'lendedAreas' => $lendedAreas ]);
    }




    public function actionContract(){//合同(暂时放弃，有bug)


        $contractModel = new ContractSearch();
        $dataProvider = $contractModel->search(Yii::$app->request->queryParams );
        Yii::trace("++++++++++++++++++++++++++++++++++++++++>");
        Yii::trace($dataProvider);




        // 获取类型(片区)
        $domainTypes   = Functions::getDomains('staff', 'sub_staff_group');

        $countries=Region::find()->where(['region_type_id'=>4])->all();//地区选择
        $model_region=ArrayHelper::map($countries,'id','region_name');
        $model_region[''] = '全部';
        ksort($model_region);


        return $this->render('contract',
            [
                'dataProvider' => $dataProvider,
                'domainTypes' => $domainTypes,
                'model_region' => $model_region ]);

    }
}